﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using Oracle.DataAccess.Client;
using System.IO;
using System.Text;

namespace Wicresoft.UnifyShow.Business
{
    public static class DocumentHelper
    {

        public static void InsertFile(HttpPostedFileBase file, string customerName, string userName, string keywords)
        {
            string mimeType = file.ContentType;
            Stream fileStream = file.InputStream;
            string fileName = Path.GetFileName(file.FileName);
            int fileLength = file.ContentLength;
            byte[] fileData = new byte[fileLength];
            fileStream.Read(fileData, 0, fileLength);
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "INSERT INTO documents (dname,FileName,DocumentFile,DocumentType,Submiter,Keywords) VALUES(:dname,:FileName,:DocumentFile,:DocumentType,:Submiter,:Keywords)";
                command.BindByName = true;
                OracleParameter dname = new OracleParameter(":dname", OracleDbType.NVarchar2, 100, customerName, System.Data.ParameterDirection.Input);
                OracleParameter FileName = new OracleParameter(":FileName", OracleDbType.NVarchar2, 100, fileName, System.Data.ParameterDirection.Input);
                OracleParameter DocumentFile = new OracleParameter(":DocumentFile", OracleDbType.Blob, fileLength, fileData, System.Data.ParameterDirection.Input);
                OracleParameter DocumentType = new OracleParameter(":DocumentType", OracleDbType.NVarchar2, 100, mimeType, System.Data.ParameterDirection.Input);
                OracleParameter Submiter = new OracleParameter(":Submiter", OracleDbType.NVarchar2, 100, userName, System.Data.ParameterDirection.Input);
                OracleParameter Keywords = new OracleParameter(":Keywords", OracleDbType.NVarchar2, 100, keywords, System.Data.ParameterDirection.Input);
                command.Parameters.Add(dname);
                command.Parameters.Add(FileName);
                command.Parameters.Add(DocumentFile);
                command.Parameters.Add(DocumentType);
                command.Parameters.Add(Submiter);
                command.Parameters.Add(Keywords);
                command.ExecuteNonQuery();
                command.Dispose();
            }

        }

        public static void GetFile(int i, out byte[] files, out string type, out string filename)
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT  filename,DocumentFile,DocumentType FROM  Documents WHERE ID=:id";
                command.BindByName = true;
                OracleParameter dname = new OracleParameter(":id", OracleDbType.Decimal, i, System.Data.ParameterDirection.Input);
                command.Parameters.Add(dname);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();
                    files = (byte[])reader["DocumentFile"];
                    type = (string)reader["DocumentType"];
                    filename = (string)reader["filename"];
                }
                command.Dispose();
            }
        }


        public static void DeleteFile(int i)
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "Delete  FROM  Documents WHERE ID=:id";
                command.BindByName = true;
                OracleParameter dname = new OracleParameter(":id", OracleDbType.Decimal, i, System.Data.ParameterDirection.Input);
                command.Parameters.Add(dname);
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public static List<DocumentModel> GetListByKeywords(string keywords)
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            List<DocumentModel> result = new List<DocumentModel>();
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT id, dname, filename,CreateTime,Submiter FROM  Documents where upper(keywords) like '%" + keywords.ToUpper() + "%' or upper(dname) like '%" + keywords.ToUpper() + "%' or upper(filename) like '%" + keywords.ToUpper() + "%'";
                DocumentModel doc = null;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        doc = new DocumentModel();
                        doc.ID = (decimal)reader["id"];
                        doc.DName = (string)reader["dname"];
                        doc.FileName = (string)reader["filename"];
                        doc.CreateTime = (DateTime)reader["CreateTime"];
                        doc.Submiter = (string)reader["Submiter"];
                        result.Add(doc);
                    }
                }
                command.Dispose();
            }
            return result;
        }

        public static List<DocumentModel> GetList()
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            List<DocumentModel> result = new List<DocumentModel>();
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT id, dname, filename,CreateTime,Submiter FROM  Documents";
                DocumentModel doc = null;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        doc = new DocumentModel();
                        doc.ID = (decimal)reader["id"];
                        doc.DName = (string)reader["dname"];
                        doc.FileName = (string)reader["filename"];
                        doc.CreateTime = (DateTime)reader["CreateTime"];
                        doc.Submiter = (string)reader["Submiter"];
                        result.Add(doc);
                    }
                }
                command.Dispose();
            }
            return result;
        }
    }
}
